package com.qilin.hammer.util;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ExcelHelper {

	
	public static void writeToExcel(ArrayList<ArrayList<String>> data, String destfile){
		WritableWorkbook wwb = null;   
        try {   
            wwb = Workbook.createWorkbook(new File(destfile));   
        } catch (IOException e) {   
            e.printStackTrace();   
        }
        if(wwb!=null){
        	WritableSheet ws = wwb.createSheet("data", 0);
        	int rowNum = data.size();
        	for(int i=0; i<rowNum; i++){
        		ArrayList<String> rowData = data.get(i);
        		for(int j=0; j<rowData.size(); j++){
        			Label label = new Label(j, i, rowData.get(j));
        			try {
						ws.addCell(label);
					} catch (RowsExceededException e) {
						e.printStackTrace();
					} catch (WriteException e) {
						e.printStackTrace();
					}        			
        		}
        	}
        	try {   
                wwb.write();   
                wwb.close();   
            } catch (IOException e) {   
                e.printStackTrace();   
            } catch (WriteException e) {   
                e.printStackTrace();   
            }    
        }
	}
	
	public static ArrayList<ArrayList<String>> readFromCSV(File file, int rowBegin){
		ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();
		try {
            BufferedReader reader = new BufferedReader(new FileReader(file));
            String line = null;
            int count = 0;
            while((line=reader.readLine())!=null){
            	if(count < rowBegin){
            		count++;
            		continue;
            	}
            	ArrayList<String> rowData = new ArrayList<String>();
                String items[] = line.split(",");   
                for(String item:items)
                	rowData.add(item);
           
                data.add(rowData);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return data;
	}
	
	public static ArrayList<ArrayList<String>> readFromExcel(File file, int rowBegin){
		Workbook wb = null;   
	    ArrayList<ArrayList<String>> data = new ArrayList<ArrayList<String>>();
		try {   
	        wb=Workbook.getWorkbook(file);
	    } catch (BiffException e) {   
	        e.printStackTrace();   
	    } catch (IOException e) {   
	        e.printStackTrace();   
	    }
	    if(wb==null)   
	        return null;
	       
	    Sheet[] sheets = wb.getSheets();
	    if(sheets!=null&&sheets.length>0){   
	        for(int i=0; i < sheets.length; i++){
	            int rowNum = sheets[i].getRows();
	        	for(int j=rowBegin; j < rowNum; j++){
	        		ArrayList<String> rowData= new ArrayList<String>();
		            Cell[] cells = sheets[i].getRow(j); 
		            if(cells!=null&&cells.length>0){   
		               for(int k=0; k<cells.length; k++)
		            	   rowData.add(cells[k].getContents());
		            }
		            data.add(rowData);
	        	}
	        }
	           
	    }     
	    wb.close();   
	    return data;
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

}
